package com.le.ui;

import com.le.component.BackGroundPanel;
import com.le.util.Plot;
import com.le.util.Query;
import com.le.util.ScreenUtils;
import org.jfree.data.category.DefaultCategoryDataset;

import javax.swing.*;
import javax.swing.border.LineBorder;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.TableModel;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.util.Arrays;
import java.util.Vector;

public class GradeQueryPage extends JPanel {
    String hostIdAddress;
    JTextField text1 = new JTextField(15);
    JButton button1 = new JButton("查       询");
    JComboBox<String> combobox1 = new JComboBox<String>();
    Font font = new Font("隶书", Font.BOLD, 28);
    JTable gradeDetailTable;
    Box tableAndPlotBox;

    public GradeQueryPage(String hostIdAddress) {
        this.hostIdAddress = hostIdAddress;
    }

    public JPanel init() {
        Box queryBox = Box.createHorizontalBox();

        combobox1.addItem("按学号查询");
        combobox1.addItem("按姓名查询");
        combobox1.setSelectedIndex(0); // 默认按学号进行查询

        queryBox.add(combobox1);
        combobox1.setFont(font);
        text1.setPreferredSize(new Dimension(180, combobox1.getHeight()));
        queryBox.add(text1);
        button1.setFont(font);
        button1.setBorder(new LineBorder(Color.orange, 2));
        queryBox.add(button1);

        Vector<String> tableHead = new Vector<>(Arrays.asList("学号", "姓名", "性别", "数学", "Java", "体育", "英语", "总分"));
        Vector<Vector<String>> nullData = new Vector<>();
        JTable table = new JTable(nullData, tableHead);
        tableAndPlotBox = Box.createVerticalBox();
        tableAndPlotBox.add(table);
        this.add(tableAndPlotBox);

        this.setLayout(new BorderLayout());
        this.add(queryBox, BorderLayout.NORTH);

        // 设置成绩详情页面
        DefaultTableCellRenderer defaultTableCellRenderer = new DefaultTableCellRenderer();
        defaultTableCellRenderer.setHorizontalAlignment(SwingConstants.CENTER);

        // 事件处理
        button1.addActionListener((ActionEvent e) -> {
            String queryModel = combobox1.getSelectedItem().toString();
            switch (queryModel) {
                case "按学号查询":
                    if (isSnoExists(text1.getText().trim())) {
                        MyThread myThread1 = new MyThread();
                        myThread1.run();
                        this.remove(tableAndPlotBox);
                        Vector<String> staticTableHead = new Vector<>(Arrays.asList("学号", "姓名", "性别", "数学", "Java", "体育", "英语", "总分"));
                        Vector<Vector<String>> data = getStudentGrade(text1.getText().trim());
                        gradeDetailTable = new JTable(data, staticTableHead);
                        gradeDetailTable.setDefaultRenderer(Object.class, defaultTableCellRenderer);
                        gradeDetailTable.setEnabled(false);
                        tableAndPlotBox = Box.createVerticalBox();
                        tableAndPlotBox.add(new JScrollPane(gradeDetailTable));
                        TableModel tableModel1 = gradeDetailTable.getModel();
                        JPanel plotPanel1 = plotViaTableData(tableModel1);
                        plotPanel1.setPreferredSize(new Dimension(480, 2100));
                        tableAndPlotBox.add(plotPanel1);
                        this.add(tableAndPlotBox);
                    } else {
                        JOptionPane.showMessageDialog(this, "学号不存在");
                    }
                    break;
                case "按姓名查询":
                    if (isSnameExists(text1.getText().trim())) {
                        MyThread myThread2 = new MyThread();
                        myThread2.run();
                        this.remove(tableAndPlotBox);
                        String sno = null;
                        sno = getSnoFromName(text1.getText().trim());
                        Vector<String> tableHead2 = new Vector<>(Arrays.asList("学号", "姓名", "性别", "数学", "Java", "体育", "英语", "总分"));
                        Vector<Vector<String>> data2 = getStudentGrade(sno);
                        gradeDetailTable = new JTable(data2, tableHead2);
                        gradeDetailTable.setDefaultRenderer(Object.class, defaultTableCellRenderer);
                        gradeDetailTable.setEnabled(false);
                        tableAndPlotBox = Box.createVerticalBox();
                        tableAndPlotBox.add(new JScrollPane(gradeDetailTable));
                        TableModel tableModel2 = gradeDetailTable.getModel();
                        JPanel plotPanel2 = plotViaTableData(tableModel2);
                        plotPanel2.setPreferredSize(new Dimension(480, 2100));
                        tableAndPlotBox.add(plotPanel2);
                        this.add(tableAndPlotBox);
                    } else JOptionPane.showMessageDialog(this, "查无此人，请检查输入");
                    break;
            }
        });
        return this;
    }

    private Vector<Vector<String>> getStudentGrade(String sno) {
        Query query = new Query(hostIdAddress);
        query.setDatabaseName("sams");

//        // 获取其专业及班级
//        query.setSQL("select major, class from student where SNO=" + sno);
//        String[][] majorAndClass = query.getRecord();
//        String major = majorAndClass[0][0];
//        String classNumber = majorAndClass[0][1];
//
//        // 获取其总分成绩在班级里的排名
//        query.setSQL("select student.sno, sum(grade) from student, sc, course\n" +
//                "where major='" + major + "' and class='" + classNumber + "' and student.SNO = sc.sno and sc.cno = course.cno\n" +
//                "group by student.sno\n" +
//                "order by sum(grade) DESC");
//        String[][] orderTable = query.getRecord();
//        int paiMing = 0;
//        for (int i = 0; i < orderTable.length; i++) {
//            if (orderTable[i][0].equals(sno)) {
//                paiMing = i + 1;
//            }
//        }

        // 获取表格数据
        String sql = "select student.sno, sname, gender, cname, grade from student, sc, course\n" +
                "where student.Sno = '" + sno + "' and student.SNO = sc.sno and sc.cno = course.cno;";
        query.setSQL(sql);
        String[][] result = query.getRecord();
        Vector<Vector<String>> studentGradeData = new Vector<>();
        for (int i = 0; i < result.length / 4; i++) {
            Vector<String> oneStudent = new Vector<>(Arrays.asList(result[4 * i]).subList(0, 3));
            int totalScore = 0;
            for (int j = 0; j < 4; j++) {
                totalScore = totalScore + Integer.parseInt(result[4 * i + j][4]);
                oneStudent.add(result[4 * i + j][4]);
            }
            oneStudent.add(Integer.toString(totalScore));
//            oneStudent.add(Integer.toString(paiMing));
            studentGradeData.add(oneStudent);
        }
        return studentGradeData;
    }

    private String getSnoFromName(String name) {
        String sno = null;
        Query query = new Query(hostIdAddress);
        query.setDatabaseName("sams");
        query.setSQL("select SNO, major, class from student where Sname='" + name + "'");
        String[][] resultSet = query.getRecord();
        if (resultSet.length > 1) {
            StringBuilder snoSet = new StringBuilder();
            for (int i = 0; i < resultSet.length; i++) {
                if (i != resultSet.length - 1) snoSet.append(resultSet[i][0]).append(", ");
                else snoSet.append((resultSet[i][0]));
            }
            sno = JOptionPane.showInputDialog("查询到姓名为" + name + "的学号记录有如下多条：" + snoSet + "请输入代表你的学号");
        } else sno = resultSet[0][0];
        return sno;
    }

    private boolean isSnoExists(String sno) {
        Query query = new Query(hostIdAddress);
        query.setDatabaseName("sams");
        query.setSQL("select * from student where sno=" + sno);
        String[][] result = query.getRecord();
        return result.length != 0;
    }

    private boolean isSnameExists(String name) {
        Query query = new Query(hostIdAddress);
        query.setDatabaseName("sams");
        query.setSQL("select * from student where sname='" + name + "'");
        String[][] result = query.getRecord();
        return result.length != 0;
    }

    private JPanel plotViaTableData(TableModel tableModel) {
        // 只在绘制上述学生成绩柱状图时有用
        DefaultCategoryDataset dataset = new DefaultCategoryDataset();
        // 获取学生成绩
        Double math = Double.parseDouble((String) tableModel.getValueAt(0, 3));
        Double java = Double.parseDouble((String) tableModel.getValueAt(0, 4));
        Double gm = Double.parseDouble((String) tableModel.getValueAt(0, 5));
        Double english = Double.parseDouble((String) tableModel.getValueAt(0, 6));

        String name = (String) tableModel.getValueAt(0, 1);
        dataset.addValue(math, name, "数学");
        dataset.addValue(java, name, "Java");
        dataset.addValue(gm, name, "体育");
        dataset.addValue(english, name, "英语");
        return Plot.plotBar(dataset, name + "成绩柱状图", "科目", "成绩");
    }

    class MyThread implements Runnable {
        @Override
        public void run() {
            JFrame jFrame = new JFrame("正在查询");
            jFrame.setBounds(ScreenUtils.getScreenWidth() / 2 - 380 / 2, ScreenUtils.getScreenHeight() / 2 - 180 / 2, 380, 180);
            JLabel jLabel = new JLabel("  这可能会花一些时间，请稍等。");
            jLabel.setFont(new Font("宋体", Font.BOLD, 22));
            jFrame.add(jLabel);
            jFrame.setVisible(true);
            jFrame.setDefaultCloseOperation(WindowConstants.HIDE_ON_CLOSE);
        }
    }
}